﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using LeHouse.Server.Dto;

namespace LeHouse.Server.Dal
{
    using ServiceStack.OrmLite;
    using System.Linq.Expressions;
    using System.Data;
    /// <summary>
    /// 创建用户信息表（抵押物权益状况）  登记记录 的DAL
    /// </summary>
    public class Collateral_PersonalDAL : GenericBaseDAL<Collateral_Personal>
    {
        /// <summary>
        /// 资产登记的数据添加And编辑
        /// </summary>
        /// <param name="parms"></param>
        /// <returns></returns>
        public bool PersonalOrBorrowerInsertOrUpdate(PersonalOrBorrower parms)
        {
            using (var cnn = DbConnection)
            {
                //开启事物
                var cbt = cnn.OpenTransaction();
                try
                {
                    if (parms.State == 1)
                    {
                        cnn.Insert(parms.Collateral_Personal);

                        foreach (Collateral_Borrower cb in parms.Collateral_BorrowerList)
                        {
                            cnn.Insert(cb);
                        }
                    }
                    if (parms.State == 1)
                    {
                        cnn.Update(parms.Collateral_Personal,
                            t => t.T_ReportNumber == parms.Collateral_Personal.T_ReportNumber);

                        cnn.Delete<Collateral_Borrower>(t => t.T_ReportNumber == parms.Collateral_Personal.T_ReportNumber);

                        foreach (Collateral_Borrower cb in parms.Collateral_BorrowerList)
                        {
                            cnn.Insert(cb);
                        }
                    }
                    //提交
                    cbt.Commit();
                    return true;
                }
                catch
                {
                    //回滚
                    cbt.Rollback();
                    return false;
                }
            }
        }

        /// <summary>
        /// 项目号递增
        /// </summary>
        /// <returns></returns>
        public string SortReportNumber()
        {
            using (var cnn = DbConnection)
            {
                var d1 = cnn.From<Collateral_Personal>().OrderByDescending(p => p.T_ReportNumber);
                var ss = cnn.Select(d1).ToList().FirstOrDefault();
                if (ss == null)
                {
                    return DateTime.Now.Year + "00001";
                }
                else
                {
                    string sortNumber = ss.T_ReportNumber.Remove(0, 4);
                    string sortNumbers = (Convert.ToInt32(sortNumber) + 1).ToString("00000");

                    string Year = ss.T_ReportNumber.Substring(0, 4);
                    if (DateTime.Now.Year > Convert.ToInt32(Year))
                    {
                        sortNumbers = "00001";
                    }
                    return DateTime.Now.Year + sortNumbers;
                }
            }
        }

        /// <summary>
        /// 勘查信息的回填
        /// </summary>
        /// <param name="ReportNumber">项目号</param>
        /// <returns></returns>   
        public SelectProspecting SelectProspecting(string ReportNumber)
        {
            using (var cnn = DbConnection)
            {
                return new SelectProspecting
                {
                    Collateral_Zone = cnn.Select<Collateral_Zone>(t => t.T_ReportNumber == ReportNumber).FirstOrDefault(),
                    Collateral_Community = cnn.Select<Collateral_Community>(t => t.T_ReportNumber == ReportNumber).FirstOrDefault(),
                    Collateral_Facility = cnn.Select<Collateral_Facility>(t => t.T_ReportNumber == ReportNumber).FirstOrDefault(),
                    Collateral_Indoor = cnn.Select<Collateral_Indoor>(t => t.T_ReportNumber == ReportNumber).FirstOrDefault(),
                    Collateral_SitePicture = cnn.Select<Collateral_SitePicture>(t => t.T_ReportNumber == ReportNumber),
                    Collateral_InquiryRecord = cnn.Select<Collateral_InquiryRecord>(t => t.T_ReportNumber == ReportNumber),
                };
            }
        }


        /// <summary>
        /// 根据报告号去查基本信息
        /// </summary>
        /// <param name="P_ReportNumber"></param>
        /// <returns></returns>
        public Jointshow SelectPersonaByReportNumber(string P_ReportNumber)
        {
            Jointshow per = new Jointshow();
            string sql = @"
select a.T_PersonalID 主键 ,a.T_ReportNumber 报告号 ,a.T_Linkman 看房联系人 ,a.T_Phone 看房联系人电话 ,a.T_Loanyear 房屋剩余年限 ,
a.T_Loanlimit 贷款额度 ,a.T_Actual 实际交易价 ,a.T_Located 房屋坐落 ,a.T_Activate 建成年代 
 ,a.T_Assess 评估受理    ,a.T_Date 日期 ,a.T_State 状态  ,b.现场勘查所属员工,b.房屋用途,b.部门,c.DictionaryName,d.T_Name  from  Collateral_Personal a  
  left join  
(select  b.T_ReportNumber, c.T_Name 现场勘查所属员工,d.T_Dname 部门,e.DictionaryName 房屋用途 from 
 Collateral_Personal b join System_Staff c on b.T_SiteSurveyid=c.T_StaffID
 left join System_Department  d on b.T_DepartmentID=d.T_DepartmentID
  left join System_Dictionary e on b.T_PurposeID=e.DictionaryID  where   b.T_ReportNumber=@P_ReportNumber )   b on a.T_ReportNumber=b.T_ReportNumber
join(
  select  DictionaryID,DictionaryName from System_Dictionary  where  DictionaryID=(  select  T_ReportID from Collateral_Personal where T_ReportNumber= @P_ReportNumber)
) c
on a.T_ReportID=c.DictionaryID
left join (
  select T_StaffID,T_Name from System_Staff where  T_StaffID=(select T_StaffID from Collateral_Personal where T_ReportNumber= @P_ReportNumber)
)d  on a.T_SiteSurveyid=d. T_StaffID
where a. T_ReportNumber=@P_ReportNumber";
            using (var cnn = DbConnection)
            {
                //var CP = cnn.CreateParam("@P_ReportNumber");
                //CP.Value = P_ReportNumber;
                //List<IDbDataParameter> ls = new List<IDbDataParameter>();
                //ls.Add(CP);
                //ls.Add(CP);
                //ls.Add(CP);
                //ls.Add(CP);
                //var list = cnn.SqlList<DataTable>(sql, ls);
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@P_ReportNumber";
                    p.Value = P_ReportNumber;
                    cmd.Parameters.Add(p);

                    try
                    {
                        var reader = cmd.ExecuteReader();
                        try
                        {
                            while (reader.Read())
                            {
                                per.T_ReportNumber = reader[1].ToString();
                                per.T_Linkman = reader[2].ToString();
                                per.T_Phone = reader[3].ToString();
                                per.T_Loanyear = reader[4].ToString();
                                per.T_Loanlimit = reader[5].ToString();
                                per.T_Actual = reader[6].ToString();
                                per.T_Located = reader[7].ToString();
                                per.T_Activate = reader[8].ToString();
                                per.T_Assess = reader[9].ToString();
                                per.T_Date = Convert.ToDateTime(reader[10]);
                                switch (Convert.ToInt32(reader[11]))
                                {
                                    case 0: per.T_State = "待约勘"; break;
                                    case 1: per.T_State = "待查勘"; break;
                                    case 2: per.T_State = "已查勘"; break;
                                    case 3: per.T_State = "待审核"; break;
                                    case 4: per.T_State = "初审未通过"; break;
                                    case 5: per.T_State = "初审通过"; break;
                                    case 6: per.T_State = "终审未通过"; break;
                                    case 7: per.T_State = "终审通过"; break;
                                    default: break;
                                }
                                per.T_State = Convert.ToInt32(reader[11]) == 0 ? "待约勘" : "暂无";
                                per.T_PurposeID = reader[13].ToString();
                                per.T_ReportID = reader[15].ToString();
                                per.T_DepartmentID = reader[14].ToString();
                                per.T_SiteSurveyid = reader[12].ToString();
                                per.T_Iid = reader[16].ToString();
                            }
                        }
                        finally
                        {
                            reader.Close();
                        }
                    }
                    catch
                    {
                        return new Jointshow();
                    }


                }
                return per;
            }
        }
        /// <summary>
        /// 根据报告号去查约没约到加载显示
        /// </summary>
        /// <param name="P_ReportNumber"></param>
        /// <returns></returns>
        public Collateral_Personal selectbyP_ReportNumberss(string P_ReportNumber)
        {
            Collateral_Personal per = new Collateral_Personal();
            string sql = @"  select T_Linkman,T_Phone,T_Located,T_PurposeID,T_State,T_Cause from Collateral_Personal where T_ReportNumber = @P_ReportNumber";
            using (var cnn = DbConnection)
            {
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@P_ReportNumber";
                    p.Value = P_ReportNumber;
                    cmd.Parameters.Add(p);
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        while (reader.Read())
                        {
                            per.T_Phone = reader["T_Phone"].ToString();
                            per.T_Located = reader["T_Located"].ToString();
                            per.T_PurposeID = Convert.ToInt32(reader["T_PurposeID"]);
                            per.T_Linkman = reader["T_State"].ToString();
                            per.T_State = Convert.ToInt32(reader["T_State"]);
                            per.T_Cause = reader["T_Cause"].ToString();

                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return per;
            }
        }



        /// <summary>
        /// WAP 端请求模糊查询地址
        /// </summary>
        /// <param name="P_located"></param>
        /// <returns></returns>
        public List<Jointshow> searchbyP_located(string C_project)
        {
            List<Jointshow> P_locatedlist = new List<Jointshow>();
            string sql = @"select DISTINCT  a.T_ReportNumber, a.T_Located,b.T_Project,c.T_Buildingafew  
                           from  Collateral_Personal a   left join Collateral_Community b 
                           on a.T_ReportNumber=b.T_ReportNumber left join Collateral_Facility c 
                           on a.T_ReportNumber=c.T_ReportNumber where T_Project like @C_project  and a.T_State=7  ";
            using (var cnn = DbConnection)
            {
                using (var cmd = cnn.CreateCommand())
                {
                    cmd.CommandText = sql;
                    var p = cmd.CreateParameter();
                    p.ParameterName = "@C_project";
                    p.Value = "%" + C_project + "%";
                    cmd.Parameters.Add(p);
                    var reader = cmd.ExecuteReader();
                    try
                    {
                        Jointshow per = new Jointshow();
                        while (reader.Read())
                        {
                            per.T_Project = reader["T_Project"].ToString();
                            per.T_Located = reader["T_Located"].ToString();
                            per.T_Buildingafew = reader["T_Buildingafew"].ToString() == null ? "暂无" : reader["T_Buildingafew"].ToString();
                            per.T_ReportNumber = reader["T_ReportNumber"].ToString();
                            P_locatedlist.Add(per);
                        }
                    }
                    finally
                    {
                        reader.Close();
                    }
                }
                return P_locatedlist;
            }
        }


        /// <summary>
        ///  查询出来以后将模糊查询小区数据的相关数据导入
        /// </summary>
        /// <param name="parms"></param>
        /// <returns></returns>
        public bool selectandupdate(string searchP_ReportNumber, string P_ReportNumbers)
        {
            using (var cnn = DbConnection)
            {
                //开启事物
                var cbt = cnn.OpenTransaction();
                try
                {
                    //抵押物区位表
                    var d1 = cnn.From<Collateral_Zone>().Where(p => p.T_ReportNumber == searchP_ReportNumber);
                    Collateral_Zone s1 = cnn.Select(d1).FirstOrDefault();
                    //公共建筑
                    var d2 = cnn.From<Collateral_Facility>().Where(p => p.T_ReportNumber == searchP_ReportNumber);
                    Collateral_Facility s2 = cnn.Select(d2).FirstOrDefault();
                    //小区
                    var d3 = cnn.From<Collateral_Community>().Where(p => p.T_ReportNumber == searchP_ReportNumber);
                    Collateral_Community s3 = cnn.Select(d3).FirstOrDefault();

                    if (s1 == null || s2 == null || s3 == null)
                    {
                        return false;
                    }
                    else
                    {
                        //抵押物区位表
                        var q1 = cnn.From<Collateral_Zone>().Where(p => p.T_ReportNumber == P_ReportNumbers);
                        Collateral_Zone w1 = cnn.Select(q1).FirstOrDefault();
                        //公共建筑
                        var q2 = cnn.From<Collateral_Facility>().Where(p => p.T_ReportNumber == P_ReportNumbers);
                        Collateral_Facility w2 = cnn.Select(q2).FirstOrDefault();
                        //小区
                        var q3 = cnn.From<Collateral_Community>().Where(p => p.T_ReportNumber == P_ReportNumbers);
                        Collateral_Community w3 = cnn.Select(q3).FirstOrDefault();
                        if (w1 != null)
                        {
                            cnn.Delete(q1);
                        }
                        if (w2 != null)
                        {
                            cnn.Delete(q2);
                        }
                        if (w3 != null)
                        {
                            cnn.Delete(q3);
                        }
                        s1.T_ReportNumber = P_ReportNumbers;
                        s2.T_ReportNumber = P_ReportNumbers;
                        s3.T_ReportNumber = P_ReportNumbers;
                        cnn.Insert(s1);
                        cnn.Insert(s2);
                        cnn.Insert(s3);
                    }
                    //提交
                    cbt.Commit();
                    return true;
                }
                catch
                {
                    //回滚
                    cbt.Rollback();
                    return false;
                }
            }
        }


        /// <summary>
        /// 审核
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool PersonalUpdateAndToexamineInsert(PersonalOrToexamine obj)
        {
            using (var cnn = DbConnection)
            {
                //开启事物
                var cbt = cnn.OpenTransaction();
                try
                {
                    cnn.Insert(obj.Toexamine);
                    cnn.Update(obj.Personal,p=>p.T_ReportNumber==obj.Personal.T_ReportNumber);
                    //提交
                    cbt.Commit();
                    return true;
                }
                catch
                {
                    //回滚
                    cbt.Rollback();
                    return false;
                }
            }
        }


        /// <summary>
        /// 删除房屋抵押信息
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public bool DeletePersonalAndOwnership(string T_ReportNumber)
        {
            using (var cnn = DbConnection)
            {
                //开启事物
                var cbt = cnn.OpenTransaction();
                try
                {
                    cnn.Delete<Collateral_Personal>(p => p.T_ReportNumber == T_ReportNumber);
                    cnn.Delete<Collateral_ownership>(p => p.T_ReportNumber ==T_ReportNumber);
                    cnn.Delete<Collateral_Realestate>(p => p.T_ReportNumber == T_ReportNumber);
                    cnn.Delete<Collateral_Taxcertificate>(p => p.T_ReportNumber == T_ReportNumber);
                    cnn.Delete<Collateral_BusinessContract>(p => p.T_ReportNumber == T_ReportNumber);
                    //提交
                    cbt.Commit();
                    return true;
                }
                catch
                {
                    //回滚
                    cbt.Rollback();
                    return false;
                }
            }
        }


    }
}

